﻿using System.Net;
using System.Text;
using Microsoft.Data.SqlClient;
using NLog;

namespace DbBackupAssist.Extensions
{
    /// <summary>
    /// 数据库操作扩展
    /// </summary>
    public static class SQLExtension
    {
        private static ILogger logger = LogManager.GetCurrentClassLogger();

        /// <summary>
        /// 启动远程数据库的xpcmd
        /// </summary>
        public static void OpenRemoteXpCmd(string connentionString)
        {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.AppendLine("EXEC sp_configure 'show advanced options', 1");
            sqlBuilder.AppendLine("RECONFIGURE ");
            sqlBuilder.AppendLine("EXEC sp_configure 'xp_cmdshell', 1");
            sqlBuilder.AppendLine("RECONFIGURE ");

            ExecuteSqlCommand(connentionString, sqlBuilder.ToString());
        }

        /// <summary>
        /// 执行net use命令引用主程序所在机器开启的共享文件夹
        /// </summary>
        /// <param name="shareName"></param>
        /// <param name="userName"></param>
        /// <param name="password"></param>
        public static void AppointShareFolder(string connentionString, string shareName, string userName, string password)
        {
            var sql = $"xp_cmdshell 'net use \\\\{ Dns.GetHostName() }\\{shareName} {password} /user:{Dns.GetHostName()}\\{userName}';";

            ExecuteSqlCommand(connentionString, sql);
        }

        /// <summary>
        /// 数据库备份
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="databaseName"></param>
        /// <returns></returns>
        public static void DbBackup(string connectionString, string databaseName, string shareFolderName ,string bakFileName)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                //备份到本地
                sql.Append("BACKUP DATABASE [");
                sql.Append(databaseName);
                sql.Append("] TO DISK=N'");
                sql.Append("\\\\" + Dns.GetHostName() + "\\" + shareFolderName);
                sql.Append("\\");
                sql.Append(bakFileName);
                sql.Append($"' WITH NOFORMAT, NAME = N'{databaseName}- Full Backup', NOINIT, SKIP, NOREWIND, NOUNLOAD");

                ExecuteSqlCommand(connectionString, sql.ToString());
            }
            catch (Exception ex)
            {
                logger.Error(ex.Message);
                logger.Error(ex.StackTrace);

                throw new SqlBackupException(databaseName);
            }
        }

        /// <summary>
        /// 数据库还原
        /// </summary>
        /// <param name="connectionStrin"></param>
        /// <param name="databaseName"></param>
        /// <param name="bakFileName"></param>
        /// <returns></returns>
        public static bool DbRestore(string connectionStrin, string databaseName, string bakFileName)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionStrin))
                {
                    connection.Open();

                    SqlCommand command = new SqlCommand();
                    command.Connection = connection;
                    command.CommandText = string.Format("select spid from sysprocesses,sysdatabases where sysprocesses.dbid=sysdatabases.dbid and sysdatabases.Name='{0}'", 
                                                        databaseName);

                    // 获取当前所有连接进程
                    List<short> list = new List<short>();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            list.Add(reader.GetInt16(0));
                        }
                        reader.Close();
                    }

                    // 杀死当前所有连接进程
                    for (int i = 0; i < list.Count; i++)
                    {
                        command = new SqlCommand(string.Format("kill {0}", list[i].ToString()), connection);
                        command.ExecuteNonQuery();
                    }

                    // 还原数据库
                    command.CommandText = string.Format("restore database {0} from disk = N'{1}' with replace", databaseName, bakFileName);
                    command.ExecuteNonQuery();

                    connection.Close();
                }                    
            }
            catch(Exception ex)
            {
                logger.Error(ex.Message);
                logger.Error(ex.StackTrace);

                throw new SqlBackupException(databaseName);
            }

            return true;
        }

        public static bool ExecuteSqlCommand(string connentionString, string commandText)
        {
            using var db = new SqlConnection(connentionString);
            db.Open();
            SqlCommand cmd = db.CreateCommand();
            cmd.Connection = db;
            cmd.CommandText = commandText;
            var affectRow = cmd.ExecuteNonQuery();
            db.Close();

            return affectRow > 0;
        }
    }
}
